CREATE USER C##SALESMANAGER IDENTIFIED BY Password_1;

ALTER SESSION SET CONTAINER=PDB1;

CREATE USER SALES1 IDENTIFIED BY Password_1;

SELECT * FROM HR.EMPLOYEES;

SELECT * FROM HR.EMPLOYEES;

SELECT * FROM EMPLOYEES;

ALTER USER C##SALESMANAGER IDENTIFIED BY Password_2;

ALTER SESSION SET CONTAINER=PDB1;

ALTER USER SALES1 IDENTIFIED BY Password_2;

DROP USER C##SALESMANAGER CASCADE;

ALTER SESSION SET CONTAINER=PDB1;

DROP USER SALES1 CASCADE;

sql / as sysdba

show pdbs

show con_name

CREATE USER C##SALESMANAGER IDENTIFIED BY Password_1;

GRANT CREATE SESSION TO C##SALESMANAGER CONTAINER=ALL;

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS 

ALTER SESSION SET CONTAINER=PDB1;

show con_name

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'C##SALESMANAGER';

ALTER SESSION SET CONTAINER=PDB2;

show con_name

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'C##SALESMANAGER';

sql C##SALESMANAGER@//localhost:1521/pdb1

sql C##SALESMANAGER@//localhost:1521/pdb2

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE USER SALES1 IDENTIFIED BY Password_1;

GRANT CREATE SESSION TO SALES1;

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SALES1';

ALTER SESSION SET CONTAINER=PDB2;

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SALES1';

sql SALES1@//localhost:1521/pdb1

show con_name

sql SALES1@//localhost:1521/pdb2



Nadawanie uprawnień

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE USER ANNA IDENTIFIED BY Password_1;

sql ANNA@//localhost:1521/pdb1

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

GRANT CREATE SESSION TO ANNA;

sql ANNA@//localhost:1521/pdb1

SELECT USER FROM DUAL;

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

REVOKE CREATE SESSION FROM ANNA;

sql ANNA@//localhost:1521/pdb1



Uprawnienia obiektowe – ćwiczenie praktyczne

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

GRANT SELECT, INSERT ON HR.EMPLOYEES TO ANNA;

GRANT CREATE SESSION TO ANNA;

sql ANNA@//localhost:1521/pdb1

SELECT employee_id, last_name FROM HR.EMPLOYEES;

SELECT privilege FROM USER_TAB_PRIVS_RECD WHERE  owner = 'HR' AND  table_name = 'EMPLOYEES' ORDER  BY privilege;

INSERT INTO HR.EMPLOYEES
       (employee_id, first_name, last_name, email, 
         hire_date, job_id, salary, department_id)
     VALUES
       (9999, 'Anna', 'Kowalska', 'ANNA_KOWALSKA9999', 
         SYSDATE, 'SA_REP', 5000, 90);

COMMIT;

SELECT employee_id, last_name FROM HR.EMPLOYEES WHERE employee_id=9999;

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

REVOKE INSERT ON HR.EMPLOYEES FROM ANNA;

sql ANNA@//localhost:1521/pdb1

INSERT INTO HR.EMPLOYEES
       (employee_id, first_name, last_name, email, 
         hire_date, job_id, salary, department_id)
     VALUES
       (9998, 'Anna', 'Kowalska', 'ANNA_KOWALSKA9998', 
         SYSDATE, 'SA_REP', 5000, 90);


SELECT employee_id, last_name FROM HR.EMPLOYEES;

SELECT privilege FROM USER_TAB_PRIVS_RECD WHERE owner = 'HR' AND  table_name = 'EMPLOYEES' ORDER  BY privilege;


Role 


sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE ROLE HR_SELECT;

GRANT SELECT ON HR.EMPLOYEES TO HR_SELECT;

GRANT SELECT ON HR.DEPARTMENTS TO HR_SELECT;

GRANT SELECT ON HR.JOBS TO HR_SELECT;

GRANT SELECT ON HR.LOCATIONS TO HR_SELECT;

GRANT HR_SELECT TO ANNA;

ALTER USER ANNA DEFAULT ROLE HR_SELECT;

CREATE ROLE HR_MODIFY;

GRANT INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO HR_MODIFY;

GRANT INSERT, UPDATE, DELETE ON HR.DEPARTMENTS TO HR_MODIFY;

GRANT HR_MODIFY TO ANNA;

ALTER USER ANNA DEFAULT ROLE HR_SELECT, HR_MODIFY;

SELECT ROLE FROM DBA_ROLES;

SELECT GRANTEE, GRANTED_ROLE, DEFAULT_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'ANNA';

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR_MODIFY';

SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HR_MODIFY';

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HR_MODIFY';

sql / as sysdba

CREATE ROLE C##PROGRAMISCI CONTAINER=ALL;

GRANT DB_DEVELOPER_ROLE TO C##PROGRAMISCI CONTAINER=ALL;

GRANT SELECT_CATALOG_ROLE TO C##PROGRAMISCI CONTAINER=ALL;

SELECT GRANTEE, GRANTED_ROLE, DEFAULT_ROLE, COMMON FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'C##PROGRAMISCI';

CREATE USER C##ADAM IDENTIFIED BY Password_1;

GRANT C##PROGRAMISCI TO C##ADAM CONTAINER=ALL;

ALTER USER C##ADAM DEFAULT ROLE C##PROGRAMISCI;



Profile użytkowników


sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='ANNA';

SELECT PROFILE, RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 730
  PASSWORD_REUSE_MAX 8
  FAILED_LOGIN_ATTEMPTS 7
  PASSWORD_LOCK_TIME 10/(24*60)
  INACTIVE_ACCOUNT_TIME 365;


CREATE PROFILE FINANCIAL LIMIT
  PASSWORD_LIFE_TIME 30
  PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 730
  PASSWORD_REUSE_MAX 8
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 20/(24*60)
  INACTIVE_ACCOUNT_TIME 365;

ALTER USER ANNA PROFILE FINANCIAL;










